﻿// =================================================================== 
// 项目说明
//====================================================================
// 
// 文件： SYS_PARAMETER_VALUES.cs
// 项目名称：

// 创建时间：2013/3/19
// 负责人：
// ===================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CommonLib.Model;
using CommonLib.IDAL;


namespace MSSQLDAL
{
    /// <summary>
    /// 数据层实例化接口类  .
    /// </summary>
    public partial class SYS_PARAMETER_VALUESDAL : ISYS_PARAMETER_VALUESDAL
    {
		//此处写查询数据的sql语句，


        //if QueryDataString==""，DAL层的GetList，GetRecordCount等函数将直接存取SYS_PARAMETER_VALUES表


        //if QueryDataString!=""，则DAL层的GetList，GetRecordCount等函数将存取该SQL语句，取代表
		//注意：所写sql语句执行结果的字段必须是唯一的


        private const string QueryDataString = "";

        public DataSet GetSysParameterList(string DeptId, string sOder)
        {
            string sqlStr = " SELECT T.DEPT_ID,T.PARAMETER_ID,F.PARAMETER_NAME,F.PARAMETER_NOTE,(CASE WHEN T.DEPT_ID='" + DeptId + "' THEN '是' ELSE '否' END ) ALLOWMODIFY,F.PARAMETER_TYPE,F.PARAMETER_OPTION,(SELECT DEPT_NAME FROM SYS_DEPT WHERE Rtrim(SYS_DEPT.DEPT_ID)=T.DEPT_ID) DEPT_NAME, (select a.PARAMETER_STR from SYS_PARAMETER_VALUES a where a.DEPT_ID=t.DEPT_ID and a.PARAMETER_ID=t.PARAMETER_ID) PARAMETER_STR,case when F.PARAMETER_TYPE=1 then '编辑框' else '选项' end PARAMETER_TYPE_NAME  FROM ( ";
            sqlStr += " SELECT MAX(DISTINCT B.DEPT_ID) DEPT_ID,B.PARAMETER_ID FROM SYS_PARAMETER_VALUES A,SYS_PARAMETER_VALUES B ";
            sqlStr += " WHERE B.DEPT_ID=Substring('" + DeptId + "',1,Len(B.DEPT_ID)) AND A.PARAMETER_ID=B.PARAMETER_ID ";
            sqlStr += " GROUP BY B.PARAMETER_ID ";
            sqlStr += " )T,SYS_PARAMETER F ";
            sqlStr += " where T.PARAMETER_ID=F.PARAMETER_ID and F.DEL_FLAG=0 and F.SHOW_FLAG=1 ";

            if (sOder.Length > 0)
                sqlStr += " order by " + sOder;
            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr);
        }
	}
}

